import sqlite3
import pymysql
import re


def case_insensitive_replace(source_str, target_str, replacement_str):
    return re.compile(re.escape(target_str), re.IGNORECASE).sub(replacement_str, source_str)#删除即可，只是为了验证某些结果写的，后续没用到

def check_sqlite_sequence(sqlite_db_path: str):
    sqlite_conn = sqlite3.connect(sqlite_db_path)
    sqlite_cursor = sqlite_conn.cursor()

    # 检查是否存在 sqlite_sequence 表
    sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='sqlite_sequence'")
    result = sqlite_cursor.fetchone()

    if result:
        print("数据库中存在 sqlite_sequence 表。")
        sqlite_conn.close()
        return True
    else:
        print("数据库中不存在 sqlite_sequence 表。")
        sqlite_conn.close()
        return False

def copy_tables_to_mysql(sqlite_db_path: str, mysql_host: str, mysql_user: str, mysql_password: str, mysql_database: str):
    # 连接到SQLite数据库
    sqlite_conn = sqlite3.connect(sqlite_db_path)
    sqlite_cursor = sqlite_conn.cursor()
    print(f"连接到SQLite数据库：{sqlite_db_path}")

    # 获取SQLite数据库中的所有表名
    sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = sqlite_cursor.fetchall()
    print(tables)
    # 检查是否存在 sqlite_sequence 表
    if check_sqlite_sequence(sqlite_db_path):
        # 如果存在，从 tables 列表中删除该表名
        tables = [table[0] for table in tables if table[0] != 'sqlite_sequence']
        print("删除 sqlite_sequence 表后的表格列表：", tables)
    # 连接到MySQL数据库
    mysql_conn = pymysql.connect(host=mysql_host,
                                 user=mysql_user,
                                 password=mysql_password,
                                 database=mysql_database,
                                 port=3306)
    mysql_cursor = mysql_conn.cursor()
    print("连接到MySQL数据库")

    failed_tables = []  # 用于存储写入失败的表名

    for table in tables:
        table_name = table
        # print(f"处理表格：{table_name}")

        # 从SQLite数据库读取数据
        sqlite_cursor.execute(f"PRAGMA table_info({table_name})")
        columns_info = sqlite_cursor.fetchall()

        sqlite_cursor.execute(f"SELECT * FROM {table_name}")
        sqlite_data = sqlite_cursor.fetchall()


        columns = []
        for column in columns_info:
            column_name = column[1]
            column_type = column[2].lower()
            columns.append(f"`{column_name}` {column_type}")

        columns_str = ', '.join(columns)
        print((columns_str))
        # 修改代码段,转化字段类型
        modified_columns_str = columns_str.replace(
            "varchar",
            "varchar(255)"  # 按需修改长度
        ).replace(
            "datetime",
            "timestamp" # 或其他日期时间类型
        ).replace(
            "integer",
            "int"
        ).replace(
            "text",
            "longtext"
        ).replace(
            "boolean",
            "bool"
        )


        print(modified_columns_str)
        # 删除MySQL表格（如果存在）
        drop_table_query = f"DROP TABLE IF EXISTS `{table_name}`"
        mysql_cursor.execute(drop_table_query)
        print(f"表 `{table_name}` 已存在并已被删除")

        # 创建MySQL表格
        create_table_query = f"CREATE TABLE `{table_name}` ({modified_columns_str})"
        mysql_cursor.execute(create_table_query)
        print(f"表 `{table_name}` 创建成功")
        # 创建MySQL表格（如果不存在）
        # create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` ({modified_columns_str});"
        # create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` ({columns_str});"
        # mysql_cursor.execute(create_table_query)

        # 删除MySQL表中的所有现有记录
        mysql_cursor.execute(f"TRUNCATE TABLE `{table_name}`")

        # 将数据插入到MySQL数据库
        insert_columns_str = ', '.join([f"`{column[1]}`" for column in columns_info])
        insert_query = f"INSERT INTO `{table_name}` ({insert_columns_str}) VALUES ({', '.join(['%s'] * len(columns_info))})"
        print(insert_query)
        try:
            mysql_cursor.executemany(insert_query, sqlite_data)
            print(f"已迁移表格 '{table_name}' 的数据")
            print("--------------------------------------------")
        except pymysql.Error as e:
            failed_tables.append(table_name)  # 记录写入失败的表名
            print(f"写入表格 '{table_name}' 的数据时出现错误：{e}")

    if failed_tables:
        print("未能成功写入数据的表格：", failed_tables)

    # 提交更改并关闭连接
    mysql_conn.commit()
    mysql_conn.close()
    sqlite_conn.close()

# 使用示例
if __name__ == "__main__":
    sqlite_db_path = r"/home/lvjie/work/LD/LD_project_06021(1)/LD_project_06021/src/main/resources/ldzddb.db"  # 指定你的 SQLite 数据库路径
    mysql_host = "localhost"  # MySQL 主机名或 IP
    mysql_user = "root"  # MySQL 用户名
    mysql_password = "lj123456"  # MySQL 密码
    mysql_database = "LD"  # 目标 MySQL 数据库名
    copy_tables_to_mysql(sqlite_db_path, mysql_host, mysql_user, mysql_password, mysql_database)

